package com.ys.web.service.impl;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ys.web.pojo.CompanyInfo;
import com.ys.web.pojo.MaterialCreateInfo;
import com.ys.web.service.CreateMaterielService;
import com.ys.web.ws.service.ManageCompanyInfoService;

/**
 * 物料生成实现类
 * 
 * @author 卓小建
 *
 */
@Service
@Transactional
public class CreateMaterielServiceImpl implements CreateMaterielService {
	private String[] promoteUnits = new String[] { "核心词", "拓展词", "高频词" };
	private static String[] extendWordDefin = new String[] { "哪家好", "好不好", "什么好", "好吗", "多少钱", "价格", "哪里有", "哪些", "靠谱",
			"公司", "厂家", "在哪里", "多少", "价格表", "价目表", "品牌", "排名", "排行", "性价比", "便宜", "安装 ", "系统", "定制", "批发", "怎么样", "如何",
			"有用吗", "好用吗" };

	@Autowired
	private ManageCompanyInfoService manageCompanyInfoService;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<MaterialCreateInfo> findMaterialCreateInfo(String industryId, String mainBussiness, int page) {
		
		StringBuffer coreKeyWord = new StringBuffer();
		StringBuffer extendKeyWord = new StringBuffer();
		List<MaterialCreateInfo> materialCreateInfos = new ArrayList<MaterialCreateInfo>();
		List<CompanyInfo> companyInfos = manageCompanyInfoService.getCompanyByIndustry_ws(industryId);
		try {
			MaterialCreateInfo materialCreateInfoPlan = new MaterialCreateInfo();// 推广计划
			materialCreateInfoPlan.setPromotionPlanName(mainBussiness);
			materialCreateInfos.add(materialCreateInfoPlan);
			// 当存在行业所在的公司时才返回查询结果
			if (!companyInfos.isEmpty()&&companyInfos!=null) {
				StringBuffer industryBuffer = new StringBuffer("and (1=0");
				StringBuffer s = new StringBuffer("and (1=0");
				for (CompanyInfo companyInfo : companyInfos) {
					s.append(" or ob.company_id =" + companyInfo.getId());
					industryBuffer.append(" or companyId = " + companyInfo.getId());
				}
				industryBuffer.append(")");
				s.append(")");
				// 核心词获取
				StringBuffer sb = new StringBuffer();
				sb.append(" select ob.keyword_search from origin_baidu as ob ");
				sb.append(" where ob.totalnumber_consult > 0 " + s.toString());
				sb.append(" and ob.keyword_search like '%").append(mainBussiness).append("%' ");
				sb.append(" GROUP BY ob.keyword_search order by ob.totalnumber_consult/ob.number_click desc limit ")
						.append(String.valueOf(0)).append(",").append("100");
				List<Map<String, Object>> lm = jdbcTemplate.queryForList(sb.toString());
				if (lm != null && !lm.isEmpty()) {
					MaterialCreateInfo materialCreateInfoUnit = new MaterialCreateInfo();// 推广单元
					materialCreateInfoUnit.setPromotionPlanName(mainBussiness);
					materialCreateInfoUnit.setPromotionUnitName(promoteUnits[0]);
					materialCreateInfos.add(materialCreateInfoUnit);
					for (int i = 0; i < lm.size(); i++) {
						coreKeyWord.append("'" + (String) lm.get(i).get("keyword_search") + "'").append(",");
						MaterialCreateInfo keywordMaterialCreateInfo = new MaterialCreateInfo();
						//该关键词出现最多的匹配模式
						String kw = (String) lm.get(i).get("keyword_search");
						String sql = "select keyword_search_mate,count(*) as num  from origin_baidu where keyword_search='"+kw+"' and keyword_search_mate !='' group by keyword_search_mate order by num desc limit 1";
						List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
						if(!list.isEmpty()){
							keywordMaterialCreateInfo.setKeywordMatchPattern((String) list.get(0).get("keyword_search_mate"));
						}
						//该关键词的行业平均价格
						StringBuffer sqlsb = new StringBuffer();
						sqlsb.append("select avg(keyword_search_price) as keyword_search_price from origin_baidu where keyword_search='").append(kw).append("'");
						sql = "select companyId from specialCompany";
						
						list = jdbcTemplate.queryForList(sql);
						if(!list.isEmpty()){
							sqlsb.append(" and company_id not in (");
							for(int j =0;j<list.size();j++){
								sqlsb.append(list.get(j).get("companyId")).append(",");
							}
							sqlsb.deleteCharAt(sqlsb.length()-1);
							sqlsb.append(")");
							
						}
						list = jdbcTemplate.queryForList(sqlsb.toString());
						if(!list.isEmpty()){
							if(list.get(0).get("keyword_search_price")!=null){
								String keywordSearchPrice = String.valueOf( list.get(0).get("keyword_search_price"));
								keywordMaterialCreateInfo.setKeywordPrice(keywordSearchPrice);
							}
						}
						
						
						
						
						keywordMaterialCreateInfo.setKeyword(kw);
						keywordMaterialCreateInfo.setPromotionPlanName(mainBussiness);
						keywordMaterialCreateInfo.setPromotionUnitName(promoteUnits[0]);
						String CorenewMethod = "SELECT ob.originality_title,ob.originality_describe_one,ob.originality_describe_two,ob.display_url FROM originality as ob "
								+ "where (ob.originality_title like '%" + (String) lm.get(i).get("keyword_search")
								+ "%' or ob.originality_describe_one " + "like '%"
								+ (String) lm.get(i).get("keyword_search") + "%' or ob.originality_describe_two like '%"
								+ (String) lm.get(i).get("keyword_search") + "%') " + s.toString()
								+ " ORDER BY ob.click_rate desc,ob.number_click desc limit 0,100";
						// System.out.println(CorenewMethod);
						List<Map<String, Object>> corenMethod = jdbcTemplate.queryForList(CorenewMethod);
						if (corenMethod != null && !corenMethod.isEmpty()) {
							keywordMaterialCreateInfo
									.setOriginalityTitle((String) corenMethod.get(0).get("originality_title"));
							keywordMaterialCreateInfo.setOriginalityDescribeOne(
									(String) corenMethod.get(0).get("originality_describe_one"));
							keywordMaterialCreateInfo.setOriginalityDescribeTwo(
									(String) corenMethod.get(0).get("originality_describe_two"));
							keywordMaterialCreateInfo
									.setOriginalityDisplayURL((String) corenMethod.get(0).get("display_url"));
							keywordMaterialCreateInfo .setPromotionPlanState("有效");
							keywordMaterialCreateInfo .setPromotionPlanEnable("启动");;
							keywordMaterialCreateInfo .setPromotionUnitState("有效");
							keywordMaterialCreateInfo .setPromotionUnitEnable("启用");
						} else {
							keywordMaterialCreateInfo.setOriginalityTitle("");
							keywordMaterialCreateInfo.setOriginalityDescribeOne("");
							keywordMaterialCreateInfo.setOriginalityDescribeTwo("");
							keywordMaterialCreateInfo.setOriginalityDisplayURL("");
							keywordMaterialCreateInfo .setPromotionPlanState("有效");
							keywordMaterialCreateInfo .setPromotionPlanEnable("启动");;
							keywordMaterialCreateInfo .setPromotionUnitState("有效");
							keywordMaterialCreateInfo .setPromotionUnitEnable("启用");
						}
						materialCreateInfos.add(keywordMaterialCreateInfo);
					}
					// 拼接关键词，以便以后面的去重复
					coreKeyWord.deleteCharAt(coreKeyWord.length() - 1);
					// System.out.println(coreKeyWord.toString());
				} // 核心词不为空

				/*
				 * 扩展词
				 */
				StringBuffer extendWordDefinSql = new StringBuffer();
				extendWordDefinSql.append("(");
				for (String extendword : extendWordDefin) {
					extendWordDefinSql.append(" OR ob.keyword_search LIKE '%").append(extendword).append("%'");
				}
				extendWordDefinSql.append(")").delete(1, 4);
				// System.out.println("包含词：" + extendWordDefinSql);
				if (!companyInfos.isEmpty()&&companyInfos!=null) {
					StringBuffer originalyCompanyIds = new StringBuffer("and (1=0");
					StringBuffer originBaiduCompanyIds = new StringBuffer("and (1=0");
					for (CompanyInfo companyInfo : companyInfos) {
						originalyCompanyIds.append(" or ob.company_id =" + companyInfo.getId());
						originBaiduCompanyIds.append(" or companyId =" + companyInfo.getId());
					}
					originalyCompanyIds.append(")");
					originBaiduCompanyIds.append(")");
					String extendWordSql = "";
					if (coreKeyWord.toString() != null && !"".equals(coreKeyWord.toString())) {
						extendWordSql = "select ob.keyword_search from origin_baidu as ob where "
								+ "ob.keyword_search like '%" + mainBussiness + "%'" + originalyCompanyIds.toString()
								+ " and LENGTH(ob.keyword_search)>=15" + " and ob.keyword_search not in ("
								+ coreKeyWord.toString() + ") and " + extendWordDefinSql
								+ " group by ob.keyword_search limit 0,100";
					} else {
						extendWordSql = "select ob.keyword_search from origin_baidu as ob where "
								+ "ob.keyword_search like '%" + mainBussiness + "%'" + originalyCompanyIds.toString()
								+ " and LENGTH(ob.keyword_search)>=15 " + " and " + extendWordDefinSql
								+ " group by ob.keyword_search limit 0,100";
					}
					// System.out.println("扩展词:" + extendWordSql);
					List<Map<String, Object>> originBaidusExtract = jdbcTemplate.queryForList(extendWordSql);
					if (originBaidusExtract != null && originBaidusExtract.size() > 0) {
						MaterialCreateInfo materialCreateInfoUnit = new MaterialCreateInfo();// 推广单元
						materialCreateInfoUnit.setPromotionPlanName(mainBussiness);
						materialCreateInfoUnit.setPromotionUnitName(promoteUnits[1]);
						materialCreateInfos.add(materialCreateInfoUnit);
						for (int i = 0; i < originBaidusExtract.size(); i++) {
							extendKeyWord.append("'" + (String) originBaidusExtract.get(i).get("keyword_search") + "'")
									.append(",");
							MaterialCreateInfo keywordMaterialCreateInfo = new MaterialCreateInfo();
							String kw = (String) originBaidusExtract.get(i).get("keyword_search");
							String sql = "select keyword_search_mate,count(*) as num  from origin_baidu where keyword_search='"+kw+"' and keyword_search_mate !='' group by keyword_search_mate order by num desc limit 1";
							List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
							if(!list.isEmpty()){
								keywordMaterialCreateInfo.setKeywordMatchPattern((String) list.get(0).get("keyword_search_mate"));
							}
							//该关键词的行业平均价格
							StringBuffer sqlsb = new StringBuffer();
							sqlsb.append("select avg(keyword_search_price) as keyword_search_price from origin_baidu where keyword_search='").append(kw).append("'");
							sql = "select companyId from specialCompany";
							
							list = jdbcTemplate.queryForList(sql);
							if(!list.isEmpty()){
								sqlsb.append(" and company_id not in (");
								for(int j =0;j<list.size();j++){
									sqlsb.append(list.get(j).get("companyId")).append(",");
								}
								sqlsb.deleteCharAt(sqlsb.length()-1);
								sqlsb.append(")");
								
							}
							list = jdbcTemplate.queryForList(sqlsb.toString());
							if(!list.isEmpty()){
								if(list.get(0).get("keyword_search_price")!=null){
									String keywordSearchPrice = String.valueOf( list.get(0).get("keyword_search_price"));
									keywordMaterialCreateInfo.setKeywordPrice(keywordSearchPrice);
								}
							}
							keywordMaterialCreateInfo.setKeyword(kw);
							keywordMaterialCreateInfo.setPromotionPlanName(mainBussiness);
							keywordMaterialCreateInfo.setPromotionUnitName(promoteUnits[1]);
							String CorenewMethod = "SELECT ob.originality_title,ob.originality_describe_one,ob.originality_describe_two,ob.display_url FROM originality as ob "
									+ "where (ob.originality_title like '%"
									+ (String) originBaidusExtract.get(i).get("keyword_search")
									+ "%' or ob.originality_describe_one " + "like '%"
									+ (String) originBaidusExtract.get(i).get("keyword_search")
									+ "%' or ob.originality_describe_two like '%"
									+ (String) originBaidusExtract.get(i).get("keyword_search") + "%') "
									+ originalyCompanyIds.toString()
									+ " ORDER BY ob.click_rate desc,ob.number_click desc limit 0,100";
							List<Map<String, Object>> extendMethod = jdbcTemplate.queryForList(CorenewMethod);
							if (extendMethod != null && !extendMethod.isEmpty()) {
								keywordMaterialCreateInfo
										.setOriginalityTitle((String) extendMethod.get(0).get("originality_title"));
								keywordMaterialCreateInfo.setOriginalityDescribeOne(
										(String) extendMethod.get(0).get("originality_describe_one"));
								keywordMaterialCreateInfo.setOriginalityDescribeTwo(
										(String) extendMethod.get(0).get("originality_describe_two"));
								keywordMaterialCreateInfo
										.setOriginalityDisplayURL((String) extendMethod.get(0).get("display_url"));
								keywordMaterialCreateInfo .setPromotionPlanState("有效");
								keywordMaterialCreateInfo .setPromotionPlanEnable("启动");;
								keywordMaterialCreateInfo .setPromotionUnitState("有效");
								keywordMaterialCreateInfo .setPromotionUnitEnable("启用");
							} else {
								keywordMaterialCreateInfo.setOriginalityTitle("");
								keywordMaterialCreateInfo.setOriginalityDescribeOne("");
								keywordMaterialCreateInfo.setOriginalityDescribeTwo("");
								keywordMaterialCreateInfo.setOriginalityDisplayURL("");
								keywordMaterialCreateInfo .setPromotionPlanState("有效");
								keywordMaterialCreateInfo .setPromotionPlanEnable("启动");;
								keywordMaterialCreateInfo .setPromotionUnitState("有效");
								keywordMaterialCreateInfo .setPromotionUnitEnable("启用");
							}
							materialCreateInfos.add(keywordMaterialCreateInfo);

							extendKeyWord.deleteCharAt(extendKeyWord.length() - 1);
							// System.out.println("extendKeyWord" +
							// extendKeyWord);
						}
					} // 扩展词不为空

					// 高频词highFrequency
					if (!companyInfos.isEmpty()&&companyInfos!=null) {
						StringBuffer highFrequencyCompanyIds = new StringBuffer("and (1=0");
						for (CompanyInfo companyInfo : companyInfos) {
							highFrequencyCompanyIds.append(" or ob.company_id =" + companyInfo.getId());
						}
						highFrequencyCompanyIds.append(")");
						StringBuffer keyword = new StringBuffer();
						if (coreKeyWord.toString() != null && !"".equals(coreKeyWord.toString())) {
							keyword.append(coreKeyWord.toString());
							if (extendKeyWord.toString() != null && !"".equals(extendKeyWord.toString())) {
								keyword.append(",").append(extendKeyWord.toString());
							}
						} else {
							if (extendKeyWord.toString() != null && !"".equals(extendKeyWord.toString())) {
								keyword.append(extendKeyWord.toString());
							}
						}
						String sql = "";
						if (keyword.toString() != null && !"".equals(keyword.toString())) {
							sql = "select ob.keyword_search from origin_baidu as ob where  ob.keyword_search like '%"
									+ mainBussiness + "%' " + highFrequencyCompanyIds
									+ " and ob.keyword_search not in (" + keyword.toString() + ")"
									+ " GROUP BY ob.keyword_search order by ob.consume desc,ob.number_click desc limit 0,100";
						} else {
							sql = "select ob.keyword_search from origin_baidu as ob where  ob.keyword_search like '%"
									+ mainBussiness + "%' " + highFrequencyCompanyIds
									+ " GROUP BY ob.keyword_search order by ob.consume desc,ob.number_click desc limit 0,100";
						}
						// System.out.println("高频词：" + sql);
						List<Map<String, Object>> lmh = jdbcTemplate.queryForList(sql);
						if (lmh != null && !lmh.isEmpty()) {
							MaterialCreateInfo materialCreateInfoUnit = new MaterialCreateInfo();// 推广单元
							materialCreateInfoUnit.setPromotionPlanName(mainBussiness);
							materialCreateInfoUnit.setPromotionUnitName(promoteUnits[2]);
							materialCreateInfos.add(materialCreateInfoUnit);
							for (int i = 0; i < lmh.size(); i++) {
								coreKeyWord.append("'" + (String) lmh.get(i).get("keyword_search") + "'").append(",");
								MaterialCreateInfo keywordMaterialCreateInfo = new MaterialCreateInfo();
								String kw = (String) lmh.get(i).get("keyword_search");
								String sqlList = "select keyword_search_mate,count(*) as num  from origin_baidu where keyword_search='"+kw+"' and keyword_search_mate !='' group by keyword_search_mate order by num desc limit 1";
								List<Map<String,Object>> list = jdbcTemplate.queryForList(sqlList);
								if(!list.isEmpty()){
									keywordMaterialCreateInfo.setKeywordMatchPattern((String) list.get(0).get("keyword_search_mate"));
								}
								//该关键词的行业平均价格
								StringBuffer sqlsb = new StringBuffer();
								sqlsb.append("select avg(keyword_search_price) as keyword_search_price from origin_baidu where keyword_search='").append(kw).append("'");
								sql = "select companyId from specialCompany";
								
								list = jdbcTemplate.queryForList(sql);
								if(!list.isEmpty()){
									sqlsb.append(" and company_id not in (");
									for(int j =0;j<list.size();j++){
										sqlsb.append(list.get(j).get("companyId")).append(",");
									}
									sqlsb.deleteCharAt(sqlsb.length()-1);
									sqlsb.append(")");
									
								}
								list = jdbcTemplate.queryForList(sqlsb.toString());
								if(!list.isEmpty()){
									if(list.get(0).get("keyword_search_price")!=null){
										String keywordSearchPrice = String.valueOf( list.get(0).get("keyword_search_price"));
										keywordMaterialCreateInfo.setKeywordPrice(keywordSearchPrice);
									}
									
									
								}
								keywordMaterialCreateInfo.setKeyword(kw);
								keywordMaterialCreateInfo.setPromotionPlanName(mainBussiness);
								keywordMaterialCreateInfo.setPromotionUnitName(promoteUnits[2]);
								String HeightnewMethod = "SELECT ob.originality_title,ob.originality_describe_one,ob.originality_describe_two,ob.display_url FROM originality as ob "
										+ "where (ob.originality_title like '%"
										+ (String) lmh.get(i).get("keyword_search")
										+ "%' or ob.originality_describe_one " + "like '%"
										+ (String) lmh.get(i).get("keyword_search")
										+ "%' or ob.originality_describe_two like '%"
										+ (String) lmh.get(i).get("keyword_search") + "%') "
										+ highFrequencyCompanyIds.toString()
										+ " ORDER BY ob.click_rate desc,ob.number_click desc limit 0,100";
								// System.out.println(CorenewMethod);
								List<Map<String, Object>> hightMethod = jdbcTemplate.queryForList(HeightnewMethod);
								if (hightMethod != null && !hightMethod.isEmpty()) {
									keywordMaterialCreateInfo
											.setOriginalityTitle((String) hightMethod.get(0).get("originality_title"));
									keywordMaterialCreateInfo.setOriginalityDescribeOne(
											(String) hightMethod.get(0).get("originality_describe_one"));
									keywordMaterialCreateInfo.setOriginalityDescribeTwo(
											(String) hightMethod.get(0).get("originality_describe_two"));
									keywordMaterialCreateInfo
											.setOriginalityDisplayURL((String) hightMethod.get(0).get("display_url"));
									keywordMaterialCreateInfo .setPromotionPlanState("有效");
									keywordMaterialCreateInfo .setPromotionPlanEnable("启动");;
									keywordMaterialCreateInfo .setPromotionUnitState("有效");
									keywordMaterialCreateInfo .setPromotionUnitEnable("启用");
								} else {
									keywordMaterialCreateInfo.setOriginalityTitle("");
									keywordMaterialCreateInfo.setOriginalityDescribeOne("");
									keywordMaterialCreateInfo.setOriginalityDescribeTwo("");
									keywordMaterialCreateInfo.setOriginalityDisplayURL("");
									keywordMaterialCreateInfo .setPromotionPlanState("有效");
									keywordMaterialCreateInfo .setPromotionPlanEnable("启动");;
									keywordMaterialCreateInfo .setPromotionUnitState("有效");
									keywordMaterialCreateInfo .setPromotionUnitEnable("启用");
								}
								materialCreateInfos.add(keywordMaterialCreateInfo);
							}
						} // 高频词不为空
						/**
						 * 下面继续添加推广单元
						 */

					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return materialCreateInfos;
	}
}
